#include <sys/types.h>
#include <sys/stat.h>
#include <fcntl.h>
#include <unistd.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <stddef.h>
#include <stdarg.h>
#include "sqlite3.h"

// 带文件名与行号log输出
#define LOG(...) printf("%s-%d: ",__FILE__, __LINE__);printf(__VA_ARGS__)

// 查询回调函数
int callback(void *data, int argc, char **argv, char **azColName);

int main(int argc, char* argv[])
{
   sqlite3_stmt * stmt;                     
   sqlite3 *db;           
   char *zErrMsg = 0;   //记录返回的错误信息
   char *sql_str;       //存sql语句
   char SQL[100] = {0};
   int sql_data = 0;    //其作为回调函数的第一个参数，可在回调函数中改变其值。
   int res;
   int fd;
   off_t fd_len;
   void *data_p;        
   int data_len;
   void *column_p;
   int column_len;

   // 打开文件
   fd = open("test.jpg", O_RDONLY);
   if(fd == -1)
   {
      LOG("Fail to open file.\r\n");
      exit(EXIT_FAILURE);
   }

   // 获取文件大小
   fd_len = lseek(fd, 0, SEEK_END);
   LOG("The file size is %ld byte.\r\n", fd_len);

   // 读取文件到内存 pread为指定光标位置读取 不改变当前光标位置
   data_p = malloc(fd_len);
   data_len = pread(fd, data_p, fd_len, 0);
   if (data_len != fd_len)
   {
      LOG("Fail to read file.\r\n");
      exit(EXIT_FAILURE);
   }
   close(fd);

   // 打开数据库
   res = sqlite3_open("test.db", &db);     
   if( res != SQLITE_OK )
   {
      LOG("Fail to open database.\r\n");
      exit(EXIT_FAILURE);
   }

   // 查询表是否存在
   sql_str = "SELECT COUNT(*) FROM sqlite_master WHERE name=='my_table';";
   res = sqlite3_exec(db, sql_str, callback, &sql_data, &zErrMsg);
   if( res != SQLITE_OK )
   {
      LOG("Select failed: %s.\r\n", zErrMsg);
      sqlite3_free(zErrMsg);
   }

   LOG("The number of tables is %d.\r\n", sql_data);

   // 表中无数据则创建
   if(sql_data == 0)
   {
      sql_str = "create table my_table( \
                  key       int   primary key   not null, \
                  data      blob                not null);"; 

      res = sqlite3_exec(db, sql_str, callback, &sql_data, &zErrMsg);
      if( res != SQLITE_OK )
      {
         LOG("Fail to create table: %s.\r\n", zErrMsg);
         sqlite3_free(zErrMsg);
      }
 
      /****************************************写二进制值****************************************/
      memset(SQL, 0, 100);
      sprintf(SQL, "insert into my_table(key, data) values(1,?)");

      // 把sql语句解析到stmt中去
      res = sqlite3_prepare_v2( db, SQL, -1, &stmt, NULL);
      if( res != SQLITE_OK )
      {
         LOG("Stmt preparation failed.\r\n");
      }
 
      // 绑定二进制数据
      res = sqlite3_bind_blob( stmt, 1, data_p, data_len, NULL); 
      if( res != SQLITE_OK )
      {
         LOG("Stmt binding failed.\r\n");
      }

      // 写入数据库
      res = sqlite3_step(stmt);
      if( res != SQLITE_DONE )
      {
         LOG("Stmt executed failed.\r\n");
      }

      // 释放资源
      free(data_p);
      res = sqlite3_finalize(stmt);
      if( res != SQLITE_OK )
      {
         LOG("Stmt released failed.\r\n");
      }

   }

   /****************************************读二进制值****************************************/
   res = sqlite3_prepare_v2( db, "select * from my_table", -1, &stmt, NULL);
   if( res != SQLITE_OK )
   {
      LOG("Stmt preparation failed.\r\n");
   }
 
   // 读数据 每循环一遍就是获取一行数据
   while(1)
   {
      res = sqlite3_step(stmt);
      if( res == SQLITE_DONE )
      {
         LOG("Stmt executed successfully.\r\n");
         break;
      }
      else if( res != SQLITE_ROW )
      {
         LOG("Stmt executed failed.\r\n");
         break;
      } 
      else
      {
         // 获取第1列的文件大小
         column_len = sqlite3_column_bytes(stmt, 1);

         // 获取第1列内容的首地址 不需要释放内存
         column_p =  (char *)sqlite3_column_blob(stmt, 1); 

         // 创建并写入文件
         fd = open("test2.jpg", O_RDWR|O_CREAT|O_TRUNC, S_IRWXU);
         if(fd == -1)
         {
            LOG("Fail to open file.\r\n");
            exit(EXIT_FAILURE);
         }

         fd_len = write(fd, column_p, column_len);
         if (fd_len != column_len)
         {
            LOG("Fail to write file.\r\n");
            exit(EXIT_FAILURE);
         }
         close(fd);
      }
   }

   res = sqlite3_finalize(stmt);
   if( res != SQLITE_OK )
   {
      LOG("Stmt released failed.\r\n");
   }

   sqlite3_close(db);

   return 0;
}


// 回调函数  打印查询的信息
int callback(void *data, int argc, char **argv, char **azColName)
{
   int i;

   *(int *)data = atoi(argv[0]);

   for(i = 0; i < argc; i++)
   {
      printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
   }

   printf("\n");
   return 0;
}
